# coding:utf-8
from contextlib import contextmanager

from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.pool import NullPool


from config import Config

conf = Config()

# 建立与mysql的连接


engine = create_engine(url=conf.MYSQL_URI, echo=False, pool_pre_ping=True, poolclass=NullPool)

# 定义模型类继承的父类及数据连接会话
DBsession = sessionmaker(bind=engine)
dbsession = scoped_session(DBsession)  # 线程安全
Base = declarative_base()
metadata = MetaData(bind=engine)


# def dbconnect():
#     from main import db
#     dbsession = db.session
#     DBase = db.Model
#     metadata = MetaData(bind=db.engine)
#     return dbsession, metadata, DBase


def dbconnect():
    return dbsession, metadata, Base


def model_list(rows):
    model_li = []
    if not rows:
        return []
    for row in rows:
        row_dict = {}
        for k, v in row.__dict__.items():
            if k.startswith('_sa_instance_state'):
                continue
            row_dict[k] = v
        model_li.append(row_dict)
    return model_li


def model_dict(row):
    dict = {}
    if row:
        for k, v in row.__dict__.items():
            if not k.startswith('_sa_instance_state'):
                dict[k] = v
    return dict


# 封装数据库连接上下文，自动实现提交，出错回滚，退出回收会话
@contextmanager
def session_maker(session=dbsession):
    try:
        yield session
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()


# 执行原生sql，获取影响的行数
def exec_sql(raw_sql):
    with session_maker() as session:
        res = session.execute(raw_sql)  # res是获取的对象
        print(res.rowcount)
        # return rows affected by an UPDATE or DELETE statement
        return res.rowcount


# 执行原生sql，获取结果list
# def fetch_sql_result(raw_sql) -> list:
#     with session_maker() as session:
#         res = session.execute(raw_sql)  # res是获取的对象
#         return model_list(res)


# 执行原生sql，获取返回结果的第一行第一列内容
def exec_sql_f(raw_sql):
    with session_maker() as session:
        res = session.execute(raw_sql)  # res是获取的对象
        data = res.first()
        # print(data[0])
        return data[0]





# def init_db():
#     # 初始化数据库，如果表不存在则创建
#     # 可以在这里import相关的表，这样下面初始化时候用上
#     # from model.settings import Settings
#     Base.metadata.create_all(bind=engine)

################

